package com.jdbc.entity;

import com.jdbc.utils.JDBCUtil;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author wzm
 * @create 2023-02-22 14:26
 */
//使用该类测试Studnet的增删改查操作
public class StudentCRUD {
    //全局变量
   private Connection conn = null;//连接
   private PreparedStatement pst = null; //执行sql
   private ResultSet resultSet = null; //结果集



    //新增学生
    @Test
    public void addStudent(){
        try {
            //1.建立与数据库的连接 获取连接对象
            conn = JDBCUtil.getConn();


            //2.定义要执行的sql
//        int id = 6;
//        String name = "张三";
//        String sql ="insert into student values("+id+","+name+")";

            //使用?来进行占位
            String sql = "insert into student values(?,?,?,?,?,?)";


            //3.获得执行sql的对象
            //使用conn.createStatement(); sql是通过拼接 会导入SQL注入问题
            //Statement statement = conn.createStatement();

            //将SQL预编译为缺占位参数的 执行sal的对象
            pst = conn.prepareStatement(sql);

            //为预编译的sql添加参数
            pst.setInt(1, 0); //第一个问号   值
            pst.setString(2, "测试");
            pst.setString(3, "男");
            pst.setInt(4, 25);
            pst.setString(5, "昆明");
            pst.setInt(6, 1);

            //4.执行sql并返回执行的结果
            //executeUpdate() 执行增删改的方法
            int i = pst.executeUpdate();

            System.out.println("受影响行数" + i);

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtil.closes(conn,pst,null);
        }


    }

    //删除学生
    @Test
    public void deleteStudent(){
        try {
            int id = 6;
            conn = JDBCUtil.getConn();
            String sql = "delete from student where id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1,id);
            int i = pst.executeUpdate();
            System.out.println("删除"+i+"条数据");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closes(conn,pst,null);
        }


    }

    //修改学生信息
    public boolean updateStudent(Student student){

        try {
            conn = JDBCUtil.getConn();
            String sql = "update student " +
                    "set stu_name = ?,stu_sex = ?,age = ?,address = ?,status = ? " +
                    "where id = ?";
            pst = conn.prepareStatement(sql);
            pst.setString(1,student.getStuName());
            pst.setString(2,student.getStuSex());
            pst.setInt(3,student.getAge());
            pst.setString(4,student.getAddress());
            pst.setInt(5,student.getStatus());
            pst.setInt(6,student.getId());
            int i = pst.executeUpdate();
            System.out.println("修改的受影响行数"+i);
            return i > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closes(conn,pst,null);
        }

        return false; //失败返回false
    }

    //查询一个学生
    public Student selectById(Student student){
        try {
            conn = JDBCUtil.getConn();
            String sql = "select * from student where id = ?";
            pst =conn.prepareStatement(sql);
            pst.setInt(1, student.getId());

            //查询返回的查询到的数据  结果集
             resultSet = pst.executeQuery();

            //将结果集转为对象 才能够在java中传递和使用
            //结果集查询后是一张表 要想获取这张表中的内容 需要将光标移动到数据上 然后通过索引 或 列名获取

            //如果光标下移一行有数据 那么就取数据
            // next()有两个作用 光标下移一行  有值返回true 无值返回false
            Student student1 = null;
            if (resultSet.next()) {

                //取结果集的数据

                //方式1通过索引
                 student1 = new Student();
                //设置对象的id为
//                student1.setId(resultSet.getInt(1));//取到结果集中第一列的值赋值给id
//                student1.setStuName(resultSet.getString(2));
//                student1.setStuSex(resultSet.getString(3));
//                student1.setAddress(resultSet.getString(4));
//                student1.setAge(resultSet.getInt(5));
//                student1.setStatus(resultSet.getInt(6));

                //方式2 通过列名 数据库的列名
                student1.setId(resultSet.getInt("id"));
                student1.setStuName(resultSet.getString("stu_name"));
                student1.setStuSex(resultSet.getString("stu_sex"));
                student1.setAddress(resultSet.getString("address"));
                student1.setAge(resultSet.getInt("age"));
                student1.setStatus(resultSet.getInt("status"));

            }
            return student1; //成功返回查询到的学生
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtil.closes(conn,pst,resultSet);
        }


        return null; //失败返回空
    }

    //查询到多个学生
    public List<Student> selectByAll(){
        try {
            conn = JDBCUtil.getConn();
            String sql = "select * from student";
            pst = conn.prepareStatement(sql);
            resultSet = pst.executeQuery();

            List<Student> list =  new ArrayList<>();
            Student student = null;
            //查询多个对象会出现多行数据 循环移动光标 循环取值
            while(resultSet.next()){
                //移动光标后有值 就取
                student = new Student();
                student.setId(resultSet.getInt(1));
                student.setStuName(resultSet.getString(2));
                student.setStuSex(resultSet.getString(3));
                student.setAge(resultSet.getInt(4));
                student.setAddress(resultSet.getString(5));
                student.setStatus(resultSet.getInt(6));

                //加到集合中
                list.add(student);
            }

            //返回一个集合
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closes(conn,pst,resultSet);
        }


        return null;//失败返回null
    }



}
